 package srdb.migration.dbhandler.srdb;
 
 import java.util.Hashtable;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import srdb.migration.Utility;
import srdb.migration.common.MTKConstants;
import srdb.migration.common.MTKGenericTransformDDL;
import srdb.migration.schema.metadata.Column;
import srdb.migration.schema.metadata.Constraint;
import srdb.migration.schema.metadata.Index;
import srdb.migration.schema.metadata.Schema;
import srdb.migration.schema.metadata.Table;
import srdb.migration.schema.metadata.View;

 
 public class PGSQLServerTransformDDL extends MTKGenericTransformDDL
 {
   public String getCreateScript(Table table)
   {
     String sql = "CREATE TABLE " + table.getTargetSchemaQualifiedName() + " (\n";
     for (int i = 0; i < table.getColumns().size(); i++) {
       sql = sql + getColumnLine(table.getColumns().get(i));
       if (i < table.getColumns().size() - 1)
         sql = sql + ", ";
       sql = sql + "\n";
     }
     sql = sql + ");\n";
 
     sql = sql + getColumnsComments(table);
 
     return sql;
   }
 
   public String getCreateScript(View view) {
     String sql = "CREATE VIEW " + escape(view.getName()) + " AS \n";
     sql = sql + view.getSQL() + "\n";
 
     String patternStr = "::([a-zA-Z]+)";
     sql = sql.replaceAll(patternStr, "");
 
     patternStr = "[\\||]";
     sql = sql.replaceAll(patternStr, "+");
 
     return sql;
   }
 
   public String getDropSchemaScript(String schemaName)
   {
     String sql = "DROP SCHEMA " + schemaName + "\n";
     return sql;
   }
 
   public String getDropScript(Schema schema)
   {
     return getDropSchemaScript(schema.getName(true));
   }
 
   public String getCreateScript(Constraint constraint)
   {
     String sql = "";
     String constTableName = constraint.getSchemaQualifiedTableName();
 
     String constName = constraint.getName(true);
     if (constName.startsWith("_")) {
       constName = "\"" + constName + "\"";
     }
 
     if ((constraint.getType() == 'P') || (constraint.getType() == 'p')) {
       sql = sql + "ALTER TABLE " + constTableName + " ADD CONSTRAINT " + constName + " PRIMARY KEY (";
 
       for (int i = 0; i < constraint.getColumns().size(); i++) {
         sql = sql + constraint.getColumns().get(i).getName();
         if (i < constraint.getColumns().size() - 1)
           sql = sql + ", ";
       }
       sql = sql + ");\n";
     } else if ((constraint.getType() == 'U') || (constraint.getType() == 'u')) {
       sql = sql + "ALTER TABLE " + constTableName + " ADD CONSTRAINT " + constName + " UNIQUE (";
 
       for (int i = 0; i < constraint.getColumns().size(); i++) {
         sql = sql + constraint.getColumns().get(i).getName();
         if (i < constraint.getColumns().size() - 1)
           sql = sql + ", ";
       }
       sql = sql + ");\n";
     } else if ((constraint.getType() == 'C') || (constraint.getType() == 'c')) {
       if (!constraint.getSearchCondition().endsWith("IS NOT NULL")) {
         String searchCondition = constraint.getSearchCondition();
         String patternStr = "::([a-zA-Z]+)";
 
         searchCondition = searchCondition.replaceAll(patternStr, "");
         patternStr = "=(\\s*[ANY]+)";
         searchCondition = searchCondition.replaceAll(patternStr, "IN");
         patternStr = "ARRAY\\[";
         searchCondition = searchCondition.replaceAll(patternStr, "");
         patternStr = "\\]\\)";
         searchCondition = searchCondition.replaceAll(patternStr, ")");
 
         sql = sql + "ALTER TABLE " + constTableName + " ADD CONSTRAINT " + constraint.getName() + " CHECK (" + searchCondition;
 
         sql = sql + ");\n";
       }
     } else if ((constraint.getType() == 'F') || (constraint.getType() == 'f')) {
       sql = sql + "ALTER TABLE " + constTableName + " ADD CONSTRAINT " + constraint.getName() + " FOREIGN KEY (";
 
       for (int i = 0; i < constraint.getColumns().size(); i++) {
         sql = sql + constraint.getColumns().get(i).getName();
         if (i < constraint.getColumns().size() - 1)
           sql = sql + ", ";
       }
       sql = sql + ") REFERENCES " + constraint.getRefIntSchemaName() + "." + constraint.getRefIntegrityTable() + " (";
       for (int i = 0; i < constraint.getRefIntegrityColumns().size(); i++) {
         sql = sql + constraint.getRefIntegrityColumns().get(i).getName();
         if (i < constraint.getRefIntegrityColumns().size() - 1)
           sql = sql + ", ";
       }
       sql = sql + ");\n";
     }
     return replaceNames(sql, getSourceToTargetNames());
   }
 
   public String getCreateScript(Index index)
   {
     String sql = "";
     String schemaName = "";
     Hashtable mappings = getSourceToTargetNames();
     if ((mappings != null) && (mappings.size() > 0)) {
       schemaName = (String)getSourceToTargetNames().get(index.getSchema());
     }
 
     sql = sql + "CREATE " + (index.isUnique() ? "UNIQUE" : "") + " INDEX " + escape(index.getName()) + " ON " + ((schemaName != null) && (!schemaName.equalsIgnoreCase("")) ? schemaName + "." : "") + index.getTableName() + " (";
 
     for (int i = 0; i < index.getColumns().size(); i++) {
       sql = sql + escape(index.getColumns().get(i).getName(), index.getColumns().get(i).isCaseSensitive());
 
       if (i < index.getColumns().size() - 1)
         sql = sql + ", ";
     }
     sql = sql + ");\n";
     return sql;
   }
 
   private String escape(String identifier, boolean caseSensitive)
   {
     if ((identifier != null) && (identifier.startsWith("\"")) && (identifier.endsWith("\"")))
     {
       return identifier;
     }
     if (!caseSensitive) {
       return Utility.escape(identifier, MTKConstants.DATABASE_TYPE.SRDB);
     }
 
     return "\"" + identifier + "\"";
   }
 
   private String escape(String identifier)
   {
     return escape(identifier, false);
   }
 
   protected String getColumnLine(Column column)
   {
     String str = "";
     String colName = column.getName();
     str = str + getIndent() + colName;
 
     Pattern tsPtrn = Pattern.compile("time(stamp)?\\s*([(]\\d+[)])?\\s*without\\s+time\\s*zone", 2);
     Matcher tsMatcher = tsPtrn.matcher(column.getDataTypeString());
 
     Pattern tswzPtrn = Pattern.compile("time(stamp)?\\s*([(]\\d+[)])?\\s*with\\s+time\\s*zone", 2);
     Matcher tswzMatcher = tswzPtrn.matcher(column.getDataTypeString());
 
     if (column.isRowID()) {
       str = str + " " + "VARCHAR";
     } else if (column.isInterval()) {
       str = str + " " + "nvarchar(255)";
     } else if (column.isGUID()) {
       str = str + " UUID";
     }
     else if ((column.getDataType() == Column.DataType.GEOMETRIC) && ((column.getDataTypeString().equalsIgnoreCase("POINT")) || (column.getDataTypeString().equalsIgnoreCase("LSEG")) || (column.getDataTypeString().equalsIgnoreCase("BOX")) || (column.getDataTypeString().equalsIgnoreCase("POLYGON")) || (column.getDataTypeString().equalsIgnoreCase("LINE")) || (column.getDataTypeString().equalsIgnoreCase("PATH"))))
     {
       str = str + " " + "geometry";
     } else if (column.getDataType() == Column.DataType.BOOLEAN) {
       str = str + " " + "bit";
     } else if ((column.getDataType() == Column.DataType.NETWORKADDRESS) && ((column.getDataTypeString().equalsIgnoreCase("CIDR")) || (column.getDataTypeString().equalsIgnoreCase("INET")) || (column.getDataTypeString().equalsIgnoreCase("MACADDR"))))
     {
       str = str + " " + "nvarchar(255)";
     }
     else if (column.getDataType() == Column.DataType.BITSTRING) {
       if (column.getDataTypeString().startsWith("bit varying"))
         str = str + " " + "varchar";
       else {
         str = str + " " + "char";
       }
 
       if (column.getLength() > 0) {
         str = str + "(" + column.getLength() + ")";
       }
     }
     else if (column.getDataType() == Column.DataType.BYTEA) {
       str = str + " " + "VARBINARY(MAX)";
     } else if (column.getDataType() == Column.DataType.TIME) {
       if (tsMatcher.matches())
         str = str + " " + "time";
       else if (tswzMatcher.matches())
         str = str + " " + "datetimeoffset";
     }
     else if (column.getDataType() == Column.DataType.TIMESTAMP) {
       if (tswzMatcher.matches())
         str = str + " " + "datetimeoffset";
       else if (tsMatcher.matches())
         str = str + " " + "datetime";
       else if (column.getDataTypeString().equalsIgnoreCase("TIMESTAMP"))
         str = str + " " + "datetime";
     }
     else if (column.getDataType() == Column.DataType.XML) {
       str = str + " " + "xml";
     } else if ((column.getDataType() == Column.DataType.INTEGER) && (column.getDataTypeString().equalsIgnoreCase("SERIAL"))) {
       str = str + " " + "int identity";
     } else if ((column.getDataType() == Column.DataType.INTEGER) && (column.getDataTypeString().equalsIgnoreCase("BIGSERIAL"))) {
       str = str + " " + "bigint identity";
     } else if (column.getDataType() == Column.DataType.TEXT) {
       str = str + " " + "text";
     }
     else if ((column.getDataType() == Column.DataType.VARCHAR) && (column.getDataTypeString().equalsIgnoreCase("bpchar"))) {
       str = str + " " + "char";
     } else {
       str = str + " " + column.getDataTypeString();
     }
 
     if ((column.getDataType() == Column.DataType.VARCHAR) || (column.getDataType() == Column.DataType.NVARCHAR))
     {
       if (column.getLength() <= 0) {
         if (!column.getDataTypeString().equalsIgnoreCase("bpchar"))
           str = str + "(MAX)";
       }
       else
         str = str + "(" + column.getLength() + ")";
     }
     else if ((column.getDataTypeString().equalsIgnoreCase("CHAR")) || (column.getDataTypeString().equalsIgnoreCase("VARCHAR")))
     {
       str = str + "(" + column.getLength() + ")";
     } else if ((column.getDataType() != Column.DataType.NUMERIC) || (!column.getDataTypeString().equalsIgnoreCase("INTEGER"))) {
       if (column.getDataTypeString().equalsIgnoreCase("NUMERIC")) {
         if (column.getPrecision() > 0) {
           str = str + "(" + column.getPrecision();
           if (column.getScale() > 0) {
             str = str + "," + column.getScale();
           }
           str = str + ")";
         }
         else
         {
           str = str + "(38,6)";
         }
       }
       else if (((column.getDataType() == Column.DataType.INTEGER) && (!column.getDataTypeString().equalsIgnoreCase("INTEGER")) && (!column.isSmallint()) && (!column.getDataTypeString().equalsIgnoreCase("INT")) && (!column.getDataTypeString().equalsIgnoreCase("BIGINT")) && (!column.getDataTypeString().equalsIgnoreCase("int identity")) && (!column.getDataTypeString().equalsIgnoreCase("bigint identity")) && (!column.getDataTypeString().equalsIgnoreCase("SERIAL")) && (!column.getDataTypeString().equalsIgnoreCase("BIGSERIAL"))) || ((column.getDataType() == Column.DataType.NUMERIC) && (!column.isReal()) && (!column.isBigInt()) && (!column.isSmallint())))
       {
         if (column.getPrecision() > 0) {
           str = str + "(" + column.getPrecision();
           if (column.getScale() > 0) {
             str = str + "," + column.getScale();
           }
           str = str + ")";
         }
       }
     }
 
     if ((column.getDefaultClause() != null) && (column.getDefaultClause().trim().length() > 0)) {
       String colDefaultClause = column.getDefaultClause();
 
       if (colDefaultClause.contains("::")) {
         String[] defaultComp = colDefaultClause.split("::");
 
         if (defaultComp.length == 2) {
           if (defaultComp[0].equalsIgnoreCase("(clock_timestamp())"))
             str = str + " DEFAULT GETDATE()";
           else if (defaultComp[1].startsWith("timestamp"))
             str = str + " DEFAULT " + defaultComp[0];
         }
       }
       else if ((colDefaultClause.equalsIgnoreCase("sysdate")) || (colDefaultClause.equalsIgnoreCase("current_date")) || (colDefaultClause.equalsIgnoreCase("now()")))
       {
         str = str + " DEFAULT GETDATE()";
       } else {
         str = str + " DEFAULT " + colDefaultClause;
       }
     }
 
     if (!column.isNullable()) {
       str = str + " NOT NULL";
     }
 
     return str;
   }
 }

/* 

 * 
 */